package com.zhaokuner.ctrl;

import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class SXSSF {
	public static void main(String[] args) throws Throwable {
		SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
													// exceeding rows will be
													// flushed to disk
		Sheet sh = wb.createSheet();
		{
			Row row = sh.createRow(0);
			Cell cell = row.createCell(0);
			cell.setCellValue("统计每自然月的数据（1~31日）报表生成日期为次月3号之前");
			sh.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
		}
		{
			Row row = sh.createRow(1);
			Cell cell = row.createCell(0);
			cell.setCellValue("本月统计");
			cell = row.createCell(2);
			cell.setCellValue("累计");
			sh.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
			sh.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
		}
		{
			Row row = sh.createRow(2);
			Cell cell = row.createCell(0);
			cell.setCellValue("本月订单数");
			cell = row.createCell(1);
			cell.setCellValue("0");
			cell = row.createCell(2);
			cell.setCellValue("订单总数");
			cell = row.createCell(3);
			cell.setCellValue("0");
		}
		{
			Row row = sh.createRow(3);
			Cell cell = row.createCell(0);
			cell.setCellValue("本月正常订单数");
			cell = row.createCell(1);
			cell.setCellValue("0");
			cell = row.createCell(2);
			cell.setCellValue("正常订单总数");
			cell = row.createCell(3);
			cell.setCellValue("0");
		}
		{
			Row row = sh.createRow(3);
			Cell cell = row.createCell(0);
			cell.setCellValue("本月退订单数");
			cell = row.createCell(1);
			cell.setCellValue("0");
			cell = row.createCell(2);
			cell.setCellValue("退订单数");
			cell = row.createCell(3);
			cell.setCellValue("0");
		}
		{
			Row row = sh.createRow(4);
			Cell cell = row.createCell(0);
			cell.setCellValue("本月退订单数");
			cell = row.createCell(1);
			cell.setCellValue("0");
			cell = row.createCell(2);
			cell.setCellValue("退订单数");
			cell = row.createCell(3);
			cell.setCellValue("0");
		}
		{
			sh.addMergedRegion(new CellRangeAddress(5, 5, 0, 3));
		}
		{
			Row row = sh.createRow(6);
			Cell cell = row.createCell(0);
			cell.setCellValue("财务对账表");
			sh.addMergedRegion(new CellRangeAddress(6, 6, 0, 3));
		}
		{
			Row row = sh.createRow(7);
			Cell cell = row.createCell(0);
			cell.setCellValue("支付方式");
			cell = row.createCell(1);
			cell.setCellValue("#月入账金额");
			cell = row.createCell(2);
			cell.setCellValue("#月退费金额");
			cell = row.createCell(3);
			cell.setCellValue("#月实际收入");
		}
		{
			Row row = sh.createRow(8);
			Cell cell = row.createCell(0);
			cell.setCellValue("微信");
			cell = row.createCell(1);
			cell.setCellValue("0");
			cell = row.createCell(2);
			cell.setCellValue("0");
			cell = row.createCell(3);
			cell.setCellValue("0");
		}
		{
			sh.addMergedRegion(new CellRangeAddress(9, 9, 0, 3));
		}
		{
			Row row = sh.createRow(10);
			Cell cell = row.createCell(0);
			cell.setCellValue("源数据表");
			sh.addMergedRegion(new CellRangeAddress(10, 10, 0, 10));
		}
		{
			Row row = sh.createRow(11);
			Cell cell = row.createCell(0);
			cell.setCellValue("#月订单明细");
			sh.addMergedRegion(new CellRangeAddress(11, 11, 0, 10));
		}
		{
			Row row = sh.createRow(12);
			Cell cell = row.createCell(0);
			cell.setCellValue("姓名");
			cell = row.createCell(1);
			cell.setCellValue("手机号");
			cell = row.createCell(2);
			cell.setCellValue("会员编号");
			cell = row.createCell(3);
			cell.setCellValue("产品编号");
			cell = row.createCell(4);
			cell.setCellValue("支付方式");
			cell = row.createCell(5);
			cell.setCellValue("（微信订单号）");
			cell = row.createCell(6);
			cell.setCellValue("（支付状态）");
			cell = row.createCell(7);
			cell.setCellValue("即时收入");
			cell = row.createCell(8);
			cell.setCellValue("分摊收入");
			cell = row.createCell(9);
			cell.setCellValue("退款金额");
			cell = row.createCell(10);
			cell.setCellValue("其他费用");
		}

		// // Rows with rownum < 900 are flushed and not accessible
		// for(int rownum = 0; rownum < 900; rownum++){
		// sh.getRow(rownum);
		// }
		//
		// // ther last 100 rows are still in memory
		// for(int rownum = 900; rownum < 1000; rownum++){
		// sh.getRow(rownum);
		// }

		Path p = Paths.get("/data/sxssf.xlsx");
		Files.deleteIfExists(p);
		Files.createFile(p);
		FileOutputStream out = new FileOutputStream(p.toString());
		wb.write(out);
		out.close();
		// dispose of temporary files backing this workbook on disk
		wb.dispose();
		wb.close();
	}
}
